/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.ufpb.halloc.dados;

import br.com.ufpb.halloc.conexaoDB.Pool;
import br.com.ufpb.halloc.conexaoDB.PoolIF;

import br.com.ufpb.halloc.exceptions.ProfessorInvalidoException;
import br.com.ufpb.halloc.negocio.Disciplina;

import br.com.ufpb.halloc.negocio.Professor;
import br.com.ufpb.halloc.negocio.Turma;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 *
 * @author Filipe
 */
public class ProfessorDAOPostgres implements ProfessorDAO {
    
    private static ProfessorDAOPostgres daoPostgres;  
    private PoolIF pool;
    
    public static ProfessorDAOPostgres getInstance(){
        if (daoPostgres == null)
            daoPostgres = new ProfessorDAOPostgres();
        return daoPostgres;
    }
    
    /* COSTRUTOR */
    private ProfessorDAOPostgres(){  
        this.pool = new Pool();
    }

    @Override
    public void salvar(Professor p) {

        if (existe(p.getMatricula())) {
            
            throw new ProfessorInvalidoException("A Matricula ja esta cadastrada!");
        } else {
            Connection con = this.pool.getConnection();
            PreparedStatement ps;

            String sqlInsert = "INSERT INTO professor (matricula, nomeprofessor, telefone, nrdepartamento)  VALUES (?, ?, ?, ?);";

            try {
                ps = con.prepareStatement(sqlInsert);
                ps.setString(1, p.getMatricula());
                ps.setString(2, p.getNome());
                ps.setString(3, p.getTelefone());
                ps.setString(4, p.getNrDepartamento());
                ps.execute();
                ps.close();

            } catch (SQLException ex) {
                Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);

            } finally {
                pool.liberarConnection(con);
            }
        }
    }
    
    public void addDisciplinasDeInteresse(){
        
    }

    @Override
    public void remover(Professor p) {
        Connection con = this.pool.getConnection();
        PreparedStatement ps;

        String sqlExcluir = "DELETE FROM professor WHERE matricula = ?;";
				
	try {
            ps = con.prepareStatement(sqlExcluir);
            ps.setString(1, p.getMatricula());
	    ps.executeUpdate();
	    ps.close();
            
         } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
         } finally {
            pool.liberarConnection(con);
	 }
    }

    @Override
    public void removerPorMatricula(String matricula) {
        Connection con = this.pool.getConnection();
        PreparedStatement ps;

        String sqlExcluir = "DELETE FROM professor WHERE matricula = ?;";
				
	try {
            ps = con.prepareStatement(sqlExcluir);
            ps.setString(1, matricula);
	    ps.executeUpdate();
	    ps.close();
            
         } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
         } finally {
            pool.liberarConnection(con);
	 }
    }

    @Override
    public void atualizar(Professor p) {
       Connection con = pool.getConnection();
       PreparedStatement ps;
       String sqlAtualizar = "UPDATE professor SET nomeprofessor = ?, telefone = ? WHERE matricula = ?;";
		
        try {
            
           ps = con.prepareStatement(sqlAtualizar);	
           
           ps.setString(1, p.getNome());
           ps.setString(2, p.getTelefone());
           ps.setString(3, p.getMatricula());
           ps.executeUpdate();
           ps.close();
        } catch (SQLException ex) {
                Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
	} finally {
            pool.liberarConnection(con);
	}
    }

    @Override
    public Professor getProfessor(Professor p) {
        Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;

        String sqlSelect = "SELECT * FROM professor WHERE matricula = ?;";
        Professor prof = new Professor();
        try {
            ps = con.prepareStatement(sqlSelect);
            ps.setString(1, p.getMatricula());
            rs = ps.executeQuery();
            prof.setMatricula(rs.getString("matricula"));
            prof.setNome(rs.getString("nomeprofessor"));
            prof.setTelefone(rs.getString("telefone"));
            ps.close();
           
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        return prof;
    }

    @Override
    public Professor getProfessorMatricula(String matricula) {
        Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;

        String sqlSelect = "SELECT matricula, nomeprofessor, telefone FROM professor WHERE matricula = ?;";
        Professor prof = new Professor();
        try {
            ps = con.prepareStatement(sqlSelect);
            ps.setString(1, matricula);
            rs = ps.executeQuery();
            while(rs.next()){
                prof.setMatricula(rs.getString("matricula"));
                prof.setNome(rs.getString("nomeprofessor"));
                prof.setTelefone(rs.getString("telefone"));
            }
            ps.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        return prof;
    }

    @Override
    public List<Professor> getProfessores() {
        Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;

        String sqlSelect = "SELECT * FROM professor ORDER BY nomeprofessor;";
        Professor prof;
        List<Professor> retorno = new ArrayList<Professor>();
        try {
            ps = con.prepareStatement(sqlSelect);
            rs = ps.executeQuery();
            while(rs.next()){
                prof = new Professor();
                prof.setMatricula(rs.getString("matricula"));
                prof.setNome(rs.getString("nomeprofessor"));
                prof.setTelefone(rs.getString("telefone"));
                retorno.add(prof);
            }   
            ps.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        return retorno;
    }

    @Override
    public int size() {
        Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;
        String sqlSelect = "SELECT COUNT(*) as total FROM professor;";
        int total = 0;
        try {
            ps = con.prepareStatement(sqlSelect);
            rs = ps.executeQuery();
            rs.next();
            total = rs.getInt("total");
                
               
            ps.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        return total;
    }

    @Override
    public List<Professor> buscarTodosComProjeto() {
       Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;

        String sqlSelect = "SELECT matricula, nomeprofessor, telefone FROM professor, projeto WHERE matricula = matProfessor GROUP BY matricula;";
        Professor prof;
        List<Professor> retorno = new ArrayList<Professor>();
        try {
            ps = con.prepareStatement(sqlSelect);
            rs = ps.executeQuery();
            while(rs.next()){
                prof = new Professor();
                prof.setMatricula(rs.getString("matricula"));
                prof.setNome(rs.getString("nomeprofessor"));
                prof.setTelefone(rs.getString("telefone"));
                retorno.add(prof);
            }   
            ps.close();
           
        } catch (SQLException ex) {
            
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        return retorno;
    }
    
    public boolean existe(String matricula){
        Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;

        String sqlSelect = "SELECT * FROM professor WHERE matricula = ?;";
        try {
            ps = con.prepareStatement(sqlSelect);
            ps.setString(1, matricula);
            rs = ps.executeQuery();
            rs.next();
            String mat = rs.getString("matricula");
            if ((mat.equals(matricula))){
                return true;
            }
            ps.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }    
        return false;
    }
    
    @Override
    public void inserirPreferencia (String codDisc, String codProf){
        Connection c = pool.getConnection();
        PreparedStatement p;
        
        String SQLQuery = "INSERT INTO disciplinas_interesse VALUES(?, ?);";
        try {
            p = c.prepareStatement(SQLQuery);
            p.setString(1, codDisc);
            p.setString(2, codProf);
            p.execute();
            p.close();
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(c);
        }
        
    }
    
    @Override
    public List<Disciplina> getDisciplinasDePreferencia(String codProf){
        Connection c = pool.getConnection();
        PreparedStatement p;
        
        List<Disciplina> l = new ArrayList<Disciplina>();
        
        String query = "select p.coddisciplina as coddisc, nomedisciplina, nrdepartamento, creditos from"+ 
"(disciplinas_interesse p join disciplina d on p.codprofessor='"+codProf +"'and d.coddisciplina = p.coddisciplina);";
        try {
            p = c.prepareStatement(query);
            ResultSet r = p.executeQuery();
            
            while(r.next()){
               
               Disciplina d = new Disciplina(r.getString("coddisc"), r.getString("nomedisciplina"));
               
               d.setNumdpto(r.getString("nrdepartamento"));
               d.setCreditos(r.getInt("creditos"));
               
               l.add(d);
           }
            p.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            pool.liberarConnection(c);
        }
        
        return l;
        
    }

    @Override
    public void alocarDisciplia(String pro, String tur) {
        Connection con = pool.getConnection();
        PreparedStatement ps;

        String sqlInsert = "INSERT INTO ministra VALUES(?,?);";


        try {
            ps = con.prepareStatement(sqlInsert);
            ps.setString(1, pro);
            ps.setString(2, tur);
                 
            
            ps.executeUpdate();
            ps.close();

        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }

    }
    
     public void desalocarDisciplia(String pro, String tur) {
        Connection con = pool.getConnection();
        PreparedStatement ps;

        String sqlExcluir = "DELETE FROM ministra where matprofessor = ? AND nrturma = ?";


       try {
            ps = con.prepareStatement(sqlExcluir);
            ps.setString(1,pro);
            ps.setString(2,tur);
	    ps.executeUpdate();
	    ps.close();

        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }

    }
    //lista de turma
    public List<Turma> turmasPreferidas(String cod) {
        Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;
        List<Turma> turmas = new ArrayList<Turma>();
        String sql = "SELECT t.nrdisciplina, t.codturma, t.nrcurso, t.nometurma FROM turma t left join ministra m on t.codturma = m.nrturma" +
                " where m.nrturma is null AND t.nrdisciplina IN (SELECT coddisciplina as nrdisciplina from disciplinas_interesse where codprofessor = ?);";

        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, cod);
            rs = ps.executeQuery();
            Turma t;
            while (rs.next()) {
                t = new Turma(rs.getString("codturma"), rs.getString("nometurma"), rs.getString("nrcurso")
                        , rs.getString("nrdisciplina"));
                
                
                turmas.add(t);
            }
            ps.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        return turmas;

    }
  
     public List<Turma> turmasAlocadasProfessor(String mat){
         List<Turma> retorno = new ArrayList<Turma>();
         Connection con = pool.getConnection();
         PreparedStatement ps;
         ResultSet rs;
         
         String sqlSelect = "SELECT t.codturma, t.nometurma, t.nrdisciplina, t.nrcurso FROM ministra m, turma t WHERE m.nrturma = t.codturma " +
                 "AND m.matprofessor=?;";
        try {
            ps = con.prepareStatement(sqlSelect);
            ps.setString(1, mat);
            rs = ps.executeQuery();
            Turma t;
            while(rs.next()){
                t= new Turma(rs.getString("codturma"), rs.getString("nometurma"), rs.getString("nrcurso"), rs.getString("nrdisciplina"));
                retorno.add(t);
            }
            ps.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
         return retorno;
     }
     
     public int numeroDeDisciplinas(String mat){
         Connection con = pool.getConnection();
         PreparedStatement ps;
         ResultSet rs;
         
         String sql = "SELECT COUNT(*) as quantidade from ministra WHERE matprofessor = ?";
         int i = 0;
         try {
            ps = con.prepareStatement(sql);
            ps.setString(1, mat);
            rs = ps.executeQuery();
            
            while(rs.next()){
                i = rs.getInt("quantidade");
            }
            ps.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
         
         
         return i;
     }
}
